﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GIMS.MODEL;

namespace KPIS.GERP.GIMS.DAL
{
    public sealed class BidRouteHistoryDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public int GetNextRevisionNo(IDBManager dbManagerTrans, int bid_seq)
        {
            try
            {
                dbManagerTrans.CreateParameters(1);
                dbManagerTrans.AddParameters(0, "@icm_bid_seq", bid_seq);
                string strSql;
                strSql = "SELECT COALESCE((SELECT COALESCE(MAX(REVISION_NO) + 1, 1)"
                        + " FROM ICM_BID_ROUTE_HISTORY "
                        + " WHERE ICM_BID_SEQ = @icm_bid_seq "
                        + " GROUP BY ICM_BID_SEQ), 1)";
                DataSet ds;
                ds = dbManagerTrans.ExecuteDataSet(CommandType.Text, strSql);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    return Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString());
                }
                throw new Exception("");
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void Insert(IList<BidRouteHistoryInfo> infoList, IDBManager dbManagerTrans)
        {
            string strSQL;
            try
            {
                DataSet ds;
                Nullable<int> lastSeq = null;
                for (int i = 0; i < infoList.Count; i++)
                {

                    dbManagerTrans.CreateParameters(11);

                    dbManagerTrans.AddParameters(0, "@revision_no", infoList[i].RevisionNo);
                    dbManagerTrans.AddParameters(1, "@icm_bid_seq", infoList[i].InfoBid.Seq);
                    dbManagerTrans.AddParameters(2, "@from_emp_seq", infoList[i].InfoFromEmp.Seq);
                    dbManagerTrans.AddParameters(3, "@to_emp_seq", infoList[i].InfoToEmp.Seq);
                    dbManagerTrans.AddParameters(4, "@remark", infoList[i].Remark);
                    dbManagerTrans.AddParameters(5, "@order_seq", infoList[i].Order);
                    lastSeq = (infoList[i].Type == "APPROVE") ? lastSeq : null;
                    dbManagerTrans.AddParameters(6, "@upper_seq", lastSeq);
                    dbManagerTrans.AddParameters(7, "@route_type", infoList[i].Type);
                    dbManagerTrans.AddParameters(8, "@route_status", infoList[i].Status);
                    dbManagerTrans.AddParameters(9, "@created_by", infoList[i].InfoRecord.Created_by);
                    dbManagerTrans.AddParameters(10, "@created_when", infoList[i].InfoRecord.Created_when);

                    strSQL = "INSERT INTO ICM_BID_ROUTE_HISTORY" 
                            + " (REVISION_NO,ICM_BID_SEQ,FROM_EMP_SEQ,TO_EMP_SEQ,REMARK,ORDER_SEQ"
                            + ", UPPER_ICM_BID_ROUTE_HIS_SEQ,ROUTE_TYPE,ROUTE_STATUS,CREATED_BY,CREATED_WHEN)"
                            + " VALUES (@revision_no"
                                + ", @icm_bid_seq"
                                + ", @from_emp_seq"
                                + ", @to_emp_seq"
                                + ", @remark"
                                + ", @order_seq"
                                + ", @upper_seq"
                                + ", @route_type"
                                + ", @route_status"
                                + ", @created_by"
                                + ", @created_when)";

                    dbManagerTrans.ExecuteNonQuery(CommandType.Text, strSQL);

                    strSQL = "SELECT LAST_INSERT_ID()"
                        + " FROM ICM_BID_ROUTE_HISTORY";
                    ds = dbManagerTrans.ExecuteDataSet(CommandType.Text, strSQL);
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        lastSeq = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}
